package com.example.wordmemory.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class WordDBHelper extends SQLiteOpenHelper {
    // 数据库信息
    private static final String DATABASE_NAME = "word_memory.db";
    private static final int DATABASE_VERSION = 1;
/**
 * 数据库表结构说明
 * 
 * 1. 用户表（users）
 *    - id: 自增主键
 *    - username: 用户名
 *    - password: 密码
 *    - name: 姓名
 *    - department: 学院
 *    - register_time: 注册时间
 * 
 * 2. 单词表（words）
 *    - id: 自增主键
 *    - word: 单词
 *    - meaning: 释义
 *    - example: 例句
 *    - modify_time: 修改时间
 * 
 * 3. 学习表（study）
 *    - id: 自增主键
 *    - word_id: 单词id（外键）
 *    - study_time: 学习时间
 *    - review_time: 复习时间
 *    - is_memorized: 是否记忆（0/1）
 *    - total_study_time: 已学习时间（分钟）
 */
    // 用户表
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_USER_ID = "id"; // 主键
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_DEPARTMENT = "department";
    public static final String COLUMN_REGISTER_TIME = "register_time";
    private static final String CREATE_TABLE_USERS =
            "CREATE TABLE " + TABLE_USERS + " ("
                    + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COLUMN_USERNAME + " TEXT NOT NULL, "
                    + COLUMN_PASSWORD + " TEXT NOT NULL, "
                    + COLUMN_NAME + " TEXT NOT NULL, "
                    + COLUMN_DEPARTMENT + " TEXT, "
                    + COLUMN_REGISTER_TIME + " TEXT NOT NULL"
                    + ")";

    // 单词表
    public static final String TABLE_WORDS = "words";
    public static final String COLUMN_WORD_ID = "id"; // 主键
    public static final String COLUMN_WORD = "word";
    public static final String COLUMN_MEANING = "meaning";
    public static final String COLUMN_EXAMPLE = "example";
    public static final String COLUMN_MODIFY_TIME = "modify_time";
    private static final String CREATE_TABLE_WORDS =
            "CREATE TABLE " + TABLE_WORDS + " ("
                    + COLUMN_WORD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COLUMN_WORD + " TEXT NOT NULL, "
                    + COLUMN_MEANING + " TEXT NOT NULL, "
                    + COLUMN_EXAMPLE + " TEXT, "
                    + COLUMN_MODIFY_TIME + " TEXT NOT NULL"
                    + ")";

    // 学习表
    public static final String TABLE_STUDY = "study";
    public static final String COLUMN_STUDY_ID = "id"; // 主键
    public static final String COLUMN_STUDY_WORD_ID = "word_id";
    public static final String COLUMN_STUDY_TIME = "study_time";
    public static final String COLUMN_REVIEW_TIME = "review_time";
    public static final String COLUMN_IS_MEMORIZED = "is_memorized";
    public static final String COLUMN_TOTAL_STUDY_TIME = "total_study_time";
    public static final String COLUMN_REVIEW_COUNT = "review_count"; // 新增：复习次数
    private static final String CREATE_TABLE_STUDY =
            "CREATE TABLE " + TABLE_STUDY + " ("
                    + COLUMN_STUDY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COLUMN_STUDY_WORD_ID + " INTEGER NOT NULL, "
                    + COLUMN_STUDY_TIME + " TEXT NOT NULL, "
                    + COLUMN_REVIEW_TIME + " TEXT, "
                    + COLUMN_IS_MEMORIZED + " INTEGER NOT NULL, "
                    + COLUMN_TOTAL_STUDY_TIME + " INTEGER, "
                    + COLUMN_REVIEW_COUNT + " INTEGER DEFAULT 0, " // 新增：复习次数，默认0
                    + "FOREIGN KEY(" + COLUMN_STUDY_WORD_ID + ") REFERENCES "
                    + TABLE_WORDS + "(" + COLUMN_WORD_ID + ")"
                    + ")";

    // ------------------- 签到/日历表 -------------------
    /**
     * calendar表结构：
     * - id: 自增主键
     * - date: 日期（yyyy-MM-dd）
     * - learn_count: 学习单词数
     * - review_count: 复习单词数
     * - is_signed: 是否签到（0/1）
     */
    public static final String TABLE_CALENDAR = "calendar";
    public static final String COLUMN_CALENDAR_ID = "id";
    public static final String COLUMN_CALENDAR_DATE = "date";
    public static final String COLUMN_CALENDAR_LEARN_COUNT = "learn_count";
    public static final String COLUMN_CALENDAR_REVIEW_COUNT = "review_count";
    public static final String COLUMN_CALENDAR_IS_SIGNED = "is_signed";
    private static final String CREATE_TABLE_CALENDAR =
            "CREATE TABLE " + TABLE_CALENDAR + " ("
                    + COLUMN_CALENDAR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + COLUMN_CALENDAR_DATE + " TEXT NOT NULL, "
                    + COLUMN_CALENDAR_LEARN_COUNT + " INTEGER DEFAULT 0, "
                    + COLUMN_CALENDAR_REVIEW_COUNT + " INTEGER DEFAULT 0, "
                    + COLUMN_CALENDAR_IS_SIGNED + " INTEGER DEFAULT 0"
                    + ")";

    // 遗忘曲线复习间隔（天）
    private static final int[] REVIEW_INTERVALS = {1, 2, 4, 7, 15, 30};

    public WordDBHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_WORDS);
        db.execSQL(CREATE_TABLE_STUDY);
        db.execSQL(CREATE_TABLE_CALENDAR);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 可根据需要实现升级逻辑
    }

    // 用户相关操作
    public long addUser(String username, String password, String name, String department, String registerTime) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_PASSWORD, password);
        values.put(COLUMN_NAME, name);
        values.put(COLUMN_DEPARTMENT, department);
        values.put(COLUMN_REGISTER_TIME, registerTime);
        return db.insert(TABLE_USERS, null, values);
    }
    public boolean validateUser(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS,
                new String[]{COLUMN_USER_ID},
                COLUMN_USERNAME + "=? AND " + COLUMN_PASSWORD + "=?",
                new String[]{username, password},
                null, null, null);
        boolean valid = (cursor.getCount() > 0);
        cursor.close();
        return valid;
    }

    /**
     * 根据用户名获取用户信息
     * @param username 用户名
     * @return Cursor，包含该用户的所有字段
     */
    public Cursor getUserByUsername(String username) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_USERS, null, COLUMN_USERNAME + "=?", new String[]{username}, null, null, null);
    }

    /**
     * 根据用户名修改用户信息
     * @param username 用户名
     * @param values ContentValues，包含要修改的字段及新值
     * @return 受影响的行数
     */
    public int updateUserByUsername(String username, ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.update(TABLE_USERS, values, COLUMN_USERNAME + "=?", new String[]{username});
    }

    // 单词相关操作
    /**
     * 新增单词
     * @param word 单词
     * @param meaning 释义
     * @param example 例句
     * @param modifyTime 修改时间
     * @return 新插入行的id
     */
    public long addWord(String word, String meaning, String example, String modifyTime) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_WORD, word);
        values.put(COLUMN_MEANING, meaning);
        values.put(COLUMN_EXAMPLE, example);
        values.put(COLUMN_MODIFY_TIME, modifyTime);
        return db.insert(TABLE_WORDS, null, values);
    }

    /**
     * 删除单词
     * @param wordId 单词id
     * @return 受影响的行数
     */
    public int deleteWord(int wordId) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_WORDS, COLUMN_WORD_ID + "=?", new String[]{String.valueOf(wordId)});
    }

    /**
     * 查询所有单词
     * @return Cursor，包含所有单词记录
     */
    public Cursor getAllWords() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_WORDS, null, null, null, null, null, null);
    }

    /**
     * 更新单词信息
     * @param wordId 单词id
     * @param values ContentValues，包含要修改的字段及新值
     * @return 受影响的行数
     */
    public int updateWord(int wordId, ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.update(TABLE_WORDS, values, COLUMN_WORD_ID + "=?", new String[]{String.valueOf(wordId)});
    }

    // 学习相关操作
    /**
     * 新增学习记录
     * @param wordId 单词id
     * @param studyTime 学习时间
     * @param reviewTime 复习时间
     * @param isMemorized 是否记忆（0/1）
     * @param totalStudyTime 已学习时间（分钟）
     * @return 新插入行的id
     */
    public long addStudyRecord(int wordId, String studyTime, String reviewTime, int isMemorized, int totalStudyTime) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_STUDY_WORD_ID, wordId);
        values.put(COLUMN_STUDY_TIME, studyTime);
        values.put(COLUMN_REVIEW_TIME, reviewTime);
        values.put(COLUMN_IS_MEMORIZED, isMemorized);
        values.put(COLUMN_TOTAL_STUDY_TIME, totalStudyTime);
        return db.insert(TABLE_STUDY, null, values);
    }

    /**
     * 删除学习记录
     * @param studyId 学习记录id
     * @return 受影响的行数
     */
    public int deleteStudyRecord(int studyId) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_STUDY, COLUMN_STUDY_ID + "=?", new String[]{String.valueOf(studyId)});
    }

    /**
     * 查询某个单词的所有学习记录
     * @param wordId 单词id
     * @return Cursor，包含该单词的所有学习记录
     */
    public Cursor getStudyRecordsByWordId(int wordId) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_STUDY, null, COLUMN_STUDY_WORD_ID + "=?", new String[]{String.valueOf(wordId)}, null, null, null);
    }

    /**
     * 更新学习记录
     * @param studyId 学习记录id
     * @param values ContentValues，包含要修改的字段及新值
     * @return 受影响的行数
     */
    public int updateStudyRecord(int studyId, ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.update(TABLE_STUDY, values, COLUMN_STUDY_ID + "=?", new String[]{String.valueOf(studyId)});
    }

    /**
     * 插入签到记录
     * @param date 日期（yyyy-MM-dd）
     * @return 新插入行的id
     */
    public long insertSignIn(String date) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_CALENDAR_DATE, date);
        values.put(COLUMN_CALENDAR_IS_SIGNED, 1);
        return db.insert(TABLE_CALENDAR, null, values);
    }

    /**
     * 查询某天是否已签到
     * @param date 日期（yyyy-MM-dd）
     * @return true已签到，false未签到
     */
    public boolean isSigned(String date) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_CALENDAR, null, COLUMN_CALENDAR_DATE + "=? AND " + COLUMN_CALENDAR_IS_SIGNED + "=1", new String[]{date}, null, null, null);
        boolean signed = cursor.getCount() > 0;
        cursor.close();
        return signed;
    }

    /**
     * 更新学习/复习单词数
     * @param date 日期
     * @param learnCount 学习单词数
     * @param reviewCount 复习单词数
     * @return 受影响行数
     */
    public int updateLearnReviewCount(String date, int learnCount, int reviewCount) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_CALENDAR_LEARN_COUNT, learnCount);
        values.put(COLUMN_CALENDAR_REVIEW_COUNT, reviewCount);
        return db.update(TABLE_CALENDAR, values, COLUMN_CALENDAR_DATE + "=?", new String[]{date});
    }

    /**
     * 查询某天的学习/复习数据
     * @param date 日期
     * @return Cursor
     */
    public Cursor getCalendarByDate(String date) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_CALENDAR, null, COLUMN_CALENDAR_DATE + "=?", new String[]{date}, null, null, null);
    }

    /**
     * 获取今日学习单词数（calendar.learn_count）
     */
    public int getTodayLearnCount() {
        String today = new java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault()).format(new java.util.Date());
        Cursor c = getCalendarByDate(today);
        int count = 0;
        if (c.moveToFirst()) {
            count = c.getInt(c.getColumnIndexOrThrow(COLUMN_CALENDAR_LEARN_COUNT));
        }
        c.close();
        return count;
    }

    /**
     * 获取今日复习单词数（calendar.review_count）
     */
    public int getTodayReviewCount() {
        String today = new java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault()).format(new java.util.Date());
        Cursor c = getCalendarByDate(today);
        int count = 0;
        if (c.moveToFirst()) {
            count = c.getInt(c.getColumnIndexOrThrow(COLUMN_CALENDAR_REVIEW_COUNT));
        }
        c.close();
        return count;
    }

    /**
     * 获取明天日期字符串 yyyy-MM-dd HH:mm:ss
     */
    public static String getTomorrowDate() {
        java.util.Calendar cal = java.util.Calendar.getInstance();
        cal.add(java.util.Calendar.DAY_OF_YEAR, 1);
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault());
        return sdf.format(cal.getTime());
    }

    /**
     * 获取base日期加days天的字符串 yyyy-MM-dd HH:mm:ss
     */
    public static String getDateAfterDays(java.util.Date base, int days) {
        java.util.Calendar cal = java.util.Calendar.getInstance();
        cal.setTime(base);
        cal.add(java.util.Calendar.DAY_OF_YEAR, days);
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault());
        return sdf.format(cal.getTime());
    }

    /**
     * 获取某单词的下次复习间隔（天）
     * 基于复习次数，依次对应REVIEW_INTERVALS
     */
    public int getNextReviewInterval(int wordId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT review_count FROM study WHERE word_id=?", new String[]{String.valueOf(wordId)});
        int interval = 1;
        if (c.moveToFirst()) {
            int reviewCount = c.getInt(0);
            if (reviewCount < REVIEW_INTERVALS.length) {
                interval = REVIEW_INTERVALS[reviewCount];
            } else {
                interval = REVIEW_INTERVALS[REVIEW_INTERVALS.length - 1];
            }
        }
        c.close();
        return interval;
    }

    /**
     * 只为未学过的单词插入study记录，study_time为当前，review_time为明天
     */
    public void insertOrUpdateStudyForLearn(int wordId, boolean isMemorized) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery("SELECT COUNT(*) FROM study WHERE word_id=?", new String[]{String.valueOf(wordId)});
        boolean exists = false;
        if (c.moveToFirst()) exists = c.getInt(0) > 0;
        c.close();
        if (!exists) {
            String now = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault()).format(new java.util.Date());
            ContentValues values = new ContentValues();
            values.put("word_id", wordId);
            values.put("study_time", now);
            values.put("review_time", getTomorrowDate());
            values.put("is_memorized", isMemorized ? 1 : 0);
            values.put("review_count", 0); // 新学习单词复习次数为0
            db.insert("study", null, values);
        }
    }

    /**
     * 复习时根据复习次数和遗忘曲线更新review_time
     */
    public void updateStudyForReview(int wordId, boolean isMemorized) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery("SELECT review_count FROM study WHERE word_id=?", new String[]{String.valueOf(wordId)});
        if (c.moveToFirst()) {
            int currentReviewCount = c.getInt(0);
            // 复习次数+1
            int newReviewCount = currentReviewCount + 1;
            // 根据新的复习次数确定下次复习间隔
            int interval = 1;
            if (newReviewCount < REVIEW_INTERVALS.length) {
                interval = REVIEW_INTERVALS[newReviewCount];
            } else {
                interval = REVIEW_INTERVALS[REVIEW_INTERVALS.length - 1];
            }
            // 基于当前时间计算下次复习时间
            java.util.Calendar cal = java.util.Calendar.getInstance();
            cal.add(java.util.Calendar.DAY_OF_YEAR, interval);
            java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault());
            String nextReview = sdf.format(cal.getTime());
            
            ContentValues values = new ContentValues();
            values.put("review_time", nextReview);
            values.put("is_memorized", isMemorized ? 1 : 0);
            values.put("review_count", newReviewCount);
            db.update("study", values, "word_id=?", new String[]{String.valueOf(wordId)});
        }
        c.close();
    }

    /**
     * 获取今天应复习的单词ID列表
     */
    public java.util.List<Integer> getTodayReviewWords() {
        SQLiteDatabase db = this.getReadableDatabase();
        String today = new java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault()).format(new java.util.Date());
        java.util.List<Integer> ids = new java.util.ArrayList<>();
        Cursor c = db.rawQuery("SELECT word_id FROM study WHERE date(review_time)=?", new String[]{today});
        while (c.moveToNext()) {
            ids.add(c.getInt(0));
        }
        c.close();
        return ids;
    }

    /**
     * 获取学习进度
     */
    public int getStudyCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT COUNT(*) FROM study", null);
        int count = 0;
        if (c.moveToFirst()) count = c.getInt(0);
        c.close();
        return count;
    }
    public int getWordsCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT COUNT(*) FROM words", null);
        int count = 0;
        if (c.moveToFirst()) count = c.getInt(0);
        c.close();
        return count;
    }

    /**
     * 获取今天实际复习的单词数（基于review_count的变化）
     */
    public int getTodayActualReviewCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        String today = new java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault()).format(new java.util.Date());
        Cursor c = db.rawQuery("SELECT COUNT(*) FROM study WHERE date(review_time)=? AND review_count > 0", new String[]{today});
        int count = 0;
        if (c.moveToFirst()) {
            count = c.getInt(0);
        }
        c.close();
        return count;
    }

    /**
     * 统计今日需要复习的单词数
     */
    public int countTodayNeedReviewWords() {
        return getTodayReviewWords().size();
    }
}
